package com.education.ai.dao.impl;

import com.education.ai.dao.ClassDao;
import com.education.ai.entity.Class;
import com.education.ai.entity.ClassVO;
import com.education.ai.util.JdbcUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.Timestamp;
import java.util.*;

/**
 * 班级数据访问实现类
 */
@Repository
public class ClassDaoImpl implements ClassDao {

    private static final Logger logger = LoggerFactory.getLogger(ClassDaoImpl.class);
    
    @Autowired
    private JdbcUtil jdbcUtil;
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Override
    public List<Class> findAll() {
        String sql = "SELECT * FROM classes";
        return mapResultsToClasses(jdbcUtil.executeQuery(sql));
    }
    
    @Override
    public Optional<Class> findById(Long id) {
        String sql = "SELECT * FROM classes WHERE id = ?";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, id);
        return results.isEmpty() ? Optional.empty() : Optional.of(mapRowToClass(results.get(0)));
    }
    
    @Override
    public Optional<Class> findByClassName(String className) {
        String sql = "SELECT * FROM classes WHERE class_name = ?";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql, className);
        return results.isEmpty() ? Optional.empty() : Optional.of(mapRowToClass(results.get(0)));
    }
    
    @Override
    public List<Class> findByGrade(String grade) {
        String sql = "SELECT * FROM classes WHERE grade = ?";
        return mapResultsToClasses(jdbcUtil.executeQuery(sql, grade));
    }
    
    @Override
    public List<Class> findByClassNameContaining(String classNamePart) {
        String sql = "SELECT * FROM classes WHERE class_name LIKE ?";
        return mapResultsToClasses(jdbcUtil.executeQuery(sql, "%" + classNamePart + "%"));
    }
    
    @Override
    public Class save(Class clazz) {
        if (clazz.getId() == null) {
            // 插入操作
            String sql = "INSERT INTO classes (class_name, grade, create_time, update_time) VALUES (?, ?, ?, ?)";
            
            // 设置创建和更新时间
            Date now = new Date();
            if (clazz.getCreateTime() == null) {
                clazz.setCreateTime(now);
            }
            if (clazz.getUpdateTime() == null) {
                clazz.setUpdateTime(now);
            }
            
            long id = jdbcUtil.executeInsert(sql, 
                    clazz.getClassName(),
                    clazz.getGrade(),
                    new Timestamp(clazz.getCreateTime().getTime()),
                    new Timestamp(clazz.getUpdateTime().getTime()));
            
            clazz.setId(id);
            return clazz;
        } else {
            // 更新操作
            update(clazz);
            return clazz;
        }
    }
    
    @Override
    public boolean update(Class clazz) {
        String sql = "UPDATE classes SET class_name = ?, grade = ?, update_time = ? WHERE id = ?";
        
        // 设置更新时间
        clazz.setUpdateTime(new Date());
        
        int rows = jdbcUtil.executeUpdate(sql,
                clazz.getClassName(),
                clazz.getGrade(),
                new Timestamp(clazz.getUpdateTime().getTime()),
                clazz.getId());
        
        return rows > 0;
    }
    
    @Override
    public boolean deleteById(Long id) {
        String sql = "DELETE FROM classes WHERE id = ?";
        int rows = jdbcUtil.executeUpdate(sql, id);
        return rows > 0;
    }
    
    @Override
    public long count() {
        String sql = "SELECT COUNT(*) AS count FROM classes";
        List<Map<String, Object>> results = jdbcUtil.executeQuery(sql);
        if (!results.isEmpty()) {
            return ((Number) results.get(0).get("count")).longValue();
        }
        return 0;
    }
    
    /**
     * 将查询结果映射为班级对象列表
     */
    private List<Class> mapResultsToClasses(List<Map<String, Object>> results) {
        List<Class> classes = new ArrayList<>();
        for (Map<String, Object> row : results) {
            classes.add(mapRowToClass(row));
        }
        return classes;
    }
    
    /**
     * 将单行结果映射为班级对象
     */
    private Class mapRowToClass(Map<String, Object> row) {
        Class clazz = new Class();
        clazz.setId(((Number) row.get("id")).longValue());
        clazz.setClassName((String) row.get("class_name"));
        clazz.setGrade((String) row.get("grade"));
        
        if (row.get("create_time") != null) {
            Timestamp timestamp = (Timestamp) row.get("create_time");
            clazz.setCreateTime(new Date(timestamp.getTime()));
        }
        
        if (row.get("update_time") != null) {
            Timestamp timestamp = (Timestamp) row.get("update_time");
            clazz.setUpdateTime(new Date(timestamp.getTime()));
        }
        
        return clazz;
    }
    
    // 从ClassMapperImpl迁移过来的方法
    @Override
    public List<ClassVO> getTeacherClasses(String teacherId) {
        try {
            logger.debug("正在查询教师ID为{}的班级列表", teacherId);
            
            // 首先查询教师信息
            String teacherSql = "SELECT t.id, t.name, t.teacher_id FROM teachers t WHERE t.teacher_id = ?";
            logger.debug("执行教师查询SQL: {}", teacherSql);
            List<Map<String, Object>> teachers = jdbcTemplate.queryForList(teacherSql, teacherId);
            
            if (teachers.isEmpty()) {
                logger.warn("未找到教师ID为{}的教师信息", teacherId);
                return new ArrayList<>();
            }
            
            Map<String, Object> teacher = teachers.get(0);
            String teacherName = (String) teacher.get("name");
            logger.debug("找到教师信息: ID={}, 姓名={}", teacher.get("id"), teacherName);
            
            // 查询班级信息
            String sql = "SELECT c.id, c.class_name as className, c.grade " +
                    "FROM classes c " +
                    "WHERE c.chinese = ? OR c.math = ? OR c.english = ? OR " +
                    "c.physics = ? OR c.chemistry = ? OR c.biology = ? OR " +
                    "c.history = ? OR c.geography = ? OR c.politics = ? " +
                    "ORDER BY c.grade, c.class_name";
            
            logger.debug("执行班级查询SQL: {}", sql);
            
            List<ClassVO> result = jdbcTemplate.query(sql, 
                (rs, rowNum) -> new ClassVO(
                    rs.getLong("id"),
                    rs.getString("className"),
                    rs.getString("grade")
                ),
                teacherName, teacherName, teacherName, 
                teacherName, teacherName, teacherName, 
                teacherName, teacherName, teacherName
            );
            
            logger.debug("查询结果: 返回了{}条记录", result.size());
            return result;
            
        } catch (Exception e) {
            // 发生错误时返回空列表
            logger.error("查询班级列表出错: {}", e.getMessage(), e);
            return new ArrayList<>();
        }
    }
    
    @Override
    public List<Map<String, Object>> getStudentsByClassId(Long classId, int offset, int limit) {
        try {
            // 先查询学生的基本信息（不包含成绩）
            String sql = "SELECT s.id, s.name, s.student_id as studentId, s.profile " +
                    "FROM students s " +
                    "WHERE s.class_id = ? " +
                    "ORDER BY s.id " +
                    "LIMIT ?, ?";
            
            logger.debug("执行查询学生SQL: {}", sql);
            logger.debug("查询参数: classId={}, offset={}, limit={}", classId, offset, limit);
            
            List<Map<String, Object>> students = jdbcTemplate.queryForList(sql, classId, offset, limit);
            logger.debug("查询结果: 返回了{}条学生记录", students.size());
            
            // 如果没有找到学生，尝试使用不含profile字段的查询
            if (students.isEmpty()) {
                logger.debug("未找到学生，尝试使用不含profile字段的查询");
                String fallbackSql = "SELECT s.id, s.name, s.student_id as studentId " +
                        "FROM students s " +
                        "WHERE s.class_id = ? " +
                        "ORDER BY s.id " +
                        "LIMIT ?, ?";
                students = jdbcTemplate.queryForList(fallbackSql, classId, offset, limit);
                logger.debug("备用查询结果: 返回了{}条学生记录", students.size());
                
                // 如果还是没有找到学生，尝试通过班级名称查询
                if (students.isEmpty()) {
                    logger.debug("通过class_id仍未找到学生，尝试通过班级名称查询");
                    
                    // 根据班级ID获取班级名称
                    String className = null;
                    if (classId == 1) {
                        className = "高一(1)班";
                    } else if (classId == 2) {
                        className = "高一(2)班";
                    } else if (classId == 3) {
                        className = "高二(1)班";
                    }
                    
                    if (className != null) {
                        logger.debug("尝试使用班级名称: {}", className);
                        
                        // 通过class_name字段查询学生
                        String classNameSql = "SELECT s.id, s.name, s.student_id as studentId " +
                                "FROM students s " +
                                "WHERE s.class_name = ? " +
                                "ORDER BY s.id " +
                                "LIMIT ?, ?";
                        students = jdbcTemplate.queryForList(classNameSql, className, offset, limit);
                        logger.debug("通过班级名称查询结果: 返回了{}条学生记录", students.size());
                    }
                }
                
                // 为每个学生添加一个默认的profile字段
                for (Map<String, Object> student : students) {
                    student.put("profile", "该学生暂无个人档案信息");
                }
            }
            
            // 为每个学生查询各科的平均成绩
            for (Map<String, Object> student : students) {
                String studentId = (String) student.get("studentId");
                
                try {
                    // 首先检查学生是否有考试记录
                    String checkRecordSql = "SELECT COUNT(*) FROM exam_records WHERE student_id = ?";
                    Integer recordCount = jdbcTemplate.queryForObject(checkRecordSql, Integer.class, studentId);
                    logger.debug("学生 {} 有 {} 条考试记录", studentId, recordCount);
                    
                    if (recordCount > 0) {
                        // 查询平均成绩
                        String avgScoreSql = "SELECT " +
                                "COALESCE(AVG(chinese), 0) as chinese, " +
                                "COALESCE(AVG(math), 0) as math, " +
                                "COALESCE(AVG(english), 0) as english, " +
                                "COALESCE(AVG(politics), 0) as politics, " +
                                "COALESCE(AVG(history), 0) as history, " +
                                "COALESCE(AVG(geography), 0) as geography, " +
                                "COALESCE(AVG(physics), 0) as physics, " +
                                "COALESCE(AVG(chemistry), 0) as chemistry, " +
                                "COALESCE(AVG(biology), 0) as biology " +
                                "FROM exam_records " +
                                "WHERE student_id = ?";
                        
                        Map<String, Object> avgScores = jdbcTemplate.queryForMap(avgScoreSql, studentId);
                        logger.debug("学生 {} 的平均成绩: {}", studentId, avgScores);
                        
                        // 将平均成绩添加到学生信息中，确保没有null值
                        for (String subject : new String[]{"chinese", "math", "english", "politics", "history", 
                                                          "geography", "physics", "chemistry", "biology"}) {
                            Object score = avgScores.get(subject);
                            // 如果成绩为null或不是数字，则设为0
                            if (score == null) {
                                student.put(subject, 0.0);
                            } else if (!(score instanceof Number)) {
                                student.put(subject, 0.0);
                            } else {
                                double scoreValue = ((Number) score).doubleValue();
                                student.put(subject, scoreValue);
                            }
                        }
                    } else {
                        // 添加默认的零分成绩
                        student.put("chinese", 0.0);
                        student.put("math", 0.0);
                        student.put("english", 0.0);
                        student.put("politics", 0.0);
                        student.put("history", 0.0);
                        student.put("geography", 0.0);
                        student.put("physics", 0.0);
                        student.put("chemistry", 0.0);
                        student.put("biology", 0.0);
                    }
                } catch (Exception e) {
                    logger.error("获取学生成绩时出错: {}", e.getMessage(), e);
                    
                    // 添加默认的零分成绩
                    student.put("chinese", 0.0);
                    student.put("math", 0.0);
                    student.put("english", 0.0);
                    student.put("politics", 0.0);
                    student.put("history", 0.0);
                    student.put("geography", 0.0);
                    student.put("physics", 0.0);
                    student.put("chemistry", 0.0);
                    student.put("biology", 0.0);
                }
            }
            
            return students;
            
        } catch (Exception e) {
            logger.error("根据班级ID查询学生列表时出错: {}", e.getMessage(), e);
            return new ArrayList<>();
        }
    }
    
    @Override
    public int countStudentsByClassId(Long classId) {
        try {
            // 首先尝试通过class_id查询
            String sql = "SELECT COUNT(*) FROM students WHERE class_id = ?";
            Integer count = jdbcTemplate.queryForObject(sql, Integer.class, classId);
            
            // 如果没有找到学生，尝试通过班级名称查询
            if (count == 0) {
                String className = null;
                if (classId == 1) {
                    className = "高一(1)班";
                } else if (classId == 2) {
                    className = "高一(2)班";
                } else if (classId == 3) {
                    className = "高二(1)班";
                }
                
                if (className != null) {
                    String classNameSql = "SELECT COUNT(*) FROM students WHERE class_name = ?";
                    count = jdbcTemplate.queryForObject(classNameSql, Integer.class, className);
                }
            }
            
            return count != null ? count : 0;
        } catch (Exception e) {
            logger.error("根据班级ID统计学生数量时出错: {}", e.getMessage(), e);
            return 0;
        }
    }
} 